﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Collections;
using System.Data;

using hes.controllers;
using hes.models;

namespace hes.data_access
{
    class AssignmentDAO
    {
        private SqlConnection db_connection; // = new SqlConnection("Data Source=sparky;Initial Catalog=cps420_team2_dev;Persist Security Info=True;User ID=cps420team2;Password=team2");

        public AssignmentDAO(SqlConnection connection)
        {
            db_connection = connection;
        }

        /// <precondition>System has received new assignment info from GUI</precondition>
        /// <summary>
        /// Adds a new assignment to the assignment list
        /// </summary>
        /// <param name="asm">Assignment object containing all assignment info needed in DB</param>
        /// <returns>integer is the ID# automatically assigned to the Assignment by the database</returns>
        /// <postcondition>New assignment has been added to the DB</postcondition>
        public int AddAssignment(Assignment asm)
        {
            int asmid = asm.AssignmentID;

            SqlCommand addasm = new SqlCommand("INSERT INTO ASSIGNMENT(CAT_ID, TITLE, PTS_POSSIBLE, POINTS_EARNED) VALUES " +
                "(" +  asm.CategoryID + ", '" + asm.Title + "', " + asm.PointsPossible + ", " + asm.PointsEarned 
                + ")", db_connection);

            SqlCommand getid = new SqlCommand("SELECT ASMT_ID from ASSIGNMENT WHERE TITLE = '" + asm.Title +
                "' and CAT_ID = " + asm.CategoryID, db_connection);

            addasm.ExecuteNonQuery();

            SqlDataReader read = getid.ExecuteReader();
            read.Read();
            int id = Convert.ToInt32(read[0].ToString());
            read.Close();
            return id;
        }

        /// <precondition>GUI has specified an assignment to be deleted</precondition>
        /// <summary>
        /// Deletes an assignment from the assignment list.
        /// </summary>
        /// <param name="asmt_id">ID# of the assignment that needs deleting</param>
        /// <postcondition>Assignment has been deleted from the database</postcondition>
        public void DeleteAssignment(int asmt_id )
        {
            SqlCommand delasm = new SqlCommand("DELETE FROM ASSIGNMENT WHERE ASMT_ID = " + asmt_id, db_connection);

            delasm.ExecuteNonQuery();
        }

        /// <precondition>GUI has specified an assignment to be modified and provided new info</precondition>
        /// <summary>
        /// An assignment is updated to reflect new information
        /// </summary>
        /// <param name="old_id">ID# of the assignment that we are changing</param>
        /// <param name="newasm">Assignment object containing all updated information</param>
        /// <postcondition>Assignment has been updated</postcondition>
        public void UpdateAssignment(int old_id, Assignment newasm)
        {
            SqlCommand update = new SqlCommand("UPDATE ASSIGNMENT SET CAT_ID=" + newasm.CategoryID +
                ", TITLE='" + newasm.Title + "', PTS_POSSIBLE=" + newasm.PointsPossible + ", POINTS_EARNED=" +
                newasm.PointsEarned + " WHERE ASMT_ID = " + old_id, db_connection);

            update.ExecuteNonQuery();
        }

        /// <precondition>System needs assignments for a category</precondition>
        /// <summary>
        /// Find all assignments for a category
        /// </summary>
        /// <param name="cat_id">ID# of the category we are looking up</param>
        /// <returns>ArrayList of assignment objects - each assignment for a category</returns>
        /// <postcondition>System now has all assignments for a given category</postcondition>
        public ArrayList FindCatAssignments(int cat_id)
        {
            SqlCommand find_assmts = new SqlCommand("SELECT * FROM ASSIGNMENT WHERE CAT_ID = " + cat_id, db_connection);

            SqlDataReader reader = find_assmts.ExecuteReader();

            ArrayList asmts = new ArrayList();
            while (reader.Read())
            {
                Assignment assmt = new Assignment();

                assmt.AssignmentID = Convert.ToInt32(reader["ASMT_ID"]);
                assmt.CategoryID = Convert.ToInt32(reader["CAT_ID"]);

                if (reader["POINTS_EARNED"] != null && reader["POINTS_EARNED"].ToString() != "")
                {
                    assmt.PointsEarned = Convert.ToInt32(reader["POINTS_EARNED"]);
                }
                if (reader["PTS_POSSIBLE"]!= null && reader["PTS_POSSIBLE"].ToString() != "")
                {
                    assmt.PointsPossible = Convert.ToInt32(reader["PTS_POSSIBLE"]);
                }

                assmt.Title = reader["TITLE"].ToString();
                asmts.Add(assmt);
            }

            reader.Close();

            return asmts;

        }

        /// <precondition>System has current student and a course the student is enrolled in</precondition>
        /// <summary>
        /// Retrieves a list of assignments for a given course/student
        /// </summary>
        /// <param name="stud">Student object that contains all info for the current student</param>
        /// <param name="course">Course object that contains all info for the current course</param>
        /// <returns>DataTable that contains all assignments and their related information</returns>
        /// <postconditions>System passes a datatable back to UI layer that will populate the assignments GUI</postconditions>
        public DataTable FindStudentAssignments(Student stud, Course course)
        {   
            StudentTermDAO stdao = new StudentTermDAO(db_connection);
            int current_term = stdao.GetCurrentStudentTerm(stud.StudentID);

            SqlCommand get_item_ids = new SqlCommand("SELECT ITEM_ID FROM TERM_ITEM WHERE STU_ID = " + 
                stud.StudentID + " and TERM_ID = " + current_term + " and COURSE_ID = '" + course.CourseID + "'", db_connection);

            SqlDataReader reader = get_item_ids.ExecuteReader();

            ArrayList itemids = new ArrayList();
            while (reader.Read())
            {
                itemids.Add(Convert.ToInt32(reader["ITEM_ID"]));
            }

            reader.Close();

            ArrayList catids = new ArrayList();
            foreach (int itemid in itemids)
            {
                SqlCommand get_cats = new SqlCommand("SELECT CAT_ID FROM ASMT_CAT WHERE ITEM_ID = " +
                    itemid, db_connection);

                reader = get_cats.ExecuteReader();

                while (reader.Read())
                {
                    catids.Add(Convert.ToInt32(reader["CAT_ID"]));
                }

                reader.Close();

            }


            if (catids.Count > 0)
            {
                string sqlarg = "SELECT * FROM ASSIGNMENT WHERE ";
                for (int i = 0; i < catids.Count; ++i)
                {
                    int catid = (int)catids[i];

                    if (i < catids.Count - 1)
                    {
                        sqlarg += " CAT_ID = " + catid + " or ";
                    }
                    else
                    {
                        sqlarg += " CAT_ID = " + catid;
                    }

                }

                //SqlCommand get_asmts = new SqlCommand(sqlarg, db_connection);
                SqlDataAdapter da = new SqlDataAdapter(sqlarg, db_connection);
                DataTable assignmentDT = new DataTable();
                da.Fill(assignmentDT);
                
                return assignmentDT;
            }
            return null;

        }

    }
}
